package cn.bestick.cblog.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import cn.bestick.cblog.dao.ArticleDao;
import cn.bestick.cblog.dmo.Article;
import cn.bestick.cblog.dmo.Category;
import cn.bestick.cblog.dto.AboutDto;
import cn.bestick.cblog.dto.ArticleDto;
import cn.bestick.cblog.dto.ArticleLiteDto;
import cn.bestick.cblog.dto.UserDto;
import cn.bestick.cblog.util.DateUtil;
import cn.bestick.cblog.util.PageBean;

public class ArticleDaoImpl extends BaseDao implements ArticleDao {
	Connection con = null;

	public List<ArticleDto> search(Article s_article)
			throws Exception {
		con = getCon();

		List<ArticleDto> articles = new ArrayList<ArticleDto>();
		String sql = "select * from t_article t1,t_category t2,t_user t3 where t1.categoryId=t2.categoryId and t1.userId = t3.userId "
				+ "and title like '%"
				+ s_article.getTitle()
				+ "%' "
				+ "or remark like '%"
				+ s_article.getRemark()
				+ "%' "
				+ "ORDER BY pubDate DESC";
				 
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			ArticleDto article = new ArticleDto();
			article.setId(rs.getInt("articleId"));
			article.setTitle(rs.getString("title"));
			article.setContent(rs.getString("content"));
			article.setRemark(rs.getString("remark"));
			article.setPubDate(DateUtil.formatString(rs.getString("pubDate"),
					"yyyy-MM-dd HH:mm:ss"));
			article.setPicture(rs.getString("picture"));
			article.setClicks(rs.getInt("clicks"));
			article.setIsDraft(rs.getInt("isDraft"));
			UserDto userDto = new UserDto();
			userDto.setId(rs.getInt("userId"));
			userDto.setImageName(rs.getString("imageName"));
			userDto.setNickname(rs.getString("nickname"));
			userDto.setWebsite(rs.getString("website"));
			article.setUser(userDto);

			Category category = new Category();
			category.setId(rs.getInt("categoryId"));
			category.setName(rs.getString("categoryName"));
			article.setCategory(category);

			articles.add(article);
		}

		close(con);
		return articles;
	}

	public List<ArticleDto> pagenation(PageBean pageBean) throws Exception {
		con = getCon();

		List<ArticleDto> articles = new ArrayList<ArticleDto>();
		String sql = "select * from t_article t1,t_category t2,t_user t3 where t1.categoryId=t2.categoryId and t1.userId = t3.userId ORDER	BY pubDate DESC limit "
				+ pageBean.getStart() + "," + pageBean.getPageSize();
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			ArticleDto article = new ArticleDto();
			article.setId(rs.getInt("articleId"));
			article.setTitle(rs.getString("title"));
			article.setContent(rs.getString("content"));
			article.setRemark(rs.getString("remark"));
			article.setPubDate(DateUtil.formatString(rs.getString("pubDate"),
					"yyyy-MM-dd HH:mm:ss"));
			article.setPicture(rs.getString("picture"));
			article.setClicks(rs.getInt("clicks"));
			article.setIsDraft(rs.getInt("isDraft"));
			UserDto userDto = new UserDto();
			userDto.setId(rs.getInt("userId"));
			userDto.setImageName(rs.getString("imageName"));
			userDto.setNickname(rs.getString("nickname"));
			userDto.setWebsite(rs.getString("website"));
			article.setUser(userDto);

			Category category = new Category();
			category.setId(rs.getInt("categoryId"));
			category.setName(rs.getString("categoryName"));
			article.setCategory(category);

			articles.add(article);
		}
		close(con);
		return articles;
	}

	public List<ArticleLiteDto> getRecentArticles() throws Exception {
		con = getCon();

		List<ArticleLiteDto> articles = new ArrayList<ArticleLiteDto>();
		String sql = "SELECT articleId,title,pubDate FROM t_article WHERE categoryId>0 ORDER BY pubDate desc limit 0,10";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			ArticleLiteDto article = new ArticleLiteDto();
			article.setId(rs.getInt("articleId"));
			article.setTitle(rs.getString("title"));
			article.setPubDate(DateUtil.formatString(rs.getString("pubDate"),
					"yyyy-MM-dd HH:mm:ss"));
			articles.add(article);
		}
		close(con);
		return articles;
	}

	public ArticleDto get(Integer id) throws Exception {
		con = getCon();

		String sql = "SELECT * FROM t_article t1,t_category t2,t_user t3 WHERE t1.categoryId=t2.categoryId AND t1.userId = t3.userId and t1.articleId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs = pstmt.executeQuery();
		ArticleDto article = new ArticleDto();
		if (rs.next()) {
			article.setId(rs.getInt("articleId"));
			article.setTitle(rs.getString("title"));
			article.setContent(rs.getString("content"));
			article.setRemark(rs.getString("remark"));
			article.setPubDate(DateUtil.formatString(rs.getString("pubDate"),
					"yyyy-MM-dd HH:mm:ss"));
			article.setPicture(rs.getString("picture"));
			article.setClicks(rs.getInt("clicks"));
			article.setIsDraft(rs.getInt("isDraft"));
			UserDto userDto = new UserDto();
			userDto.setId(rs.getInt("userId"));
			userDto.setImageName(rs.getString("imageName"));
			userDto.setNickname(rs.getString("nickname"));
			userDto.setWebsite(rs.getString("website"));
			article.setUser(userDto);

			Category category = new Category();
			category.setId(rs.getInt("categoryId"));
			category.setName(rs.getString("categoryName"));
			article.setCategory(category);

		}

		close(con);
		return article;
	}

	public ArticleLiteDto getPre(Integer id) throws Exception {
		con = getCon();

		String sql = "SELECT * FROM t_article WHERE articleId = (SELECT articleId FROM t_article WHERE articleId < ? and categoryId > 0 ORDER BY articleId DESC LIMIT 1);";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs = pstmt.executeQuery();
		ArticleLiteDto article = new ArticleLiteDto();
		if (rs.next()) {
			article.setId(rs.getInt("articleId"));
			article.setTitle(rs.getString("title"));
		}

		close(con);
		return article;
	}

	public ArticleLiteDto getNext(Integer id) throws Exception {
		con = getCon();

		String sql = "SELECT * FROM t_article WHERE articleId = (SELECT articleId FROM t_article WHERE articleId > ? and categoryId > 0 ORDER BY articleId asc LIMIT 1);";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, id);
		ResultSet rs = pstmt.executeQuery();
		ArticleLiteDto article = new ArticleLiteDto();
		if (rs.next()) {
			article.setId(rs.getInt("articleId"));
			article.setTitle(rs.getString("title"));
		}

		close(con);
		return article;
	}

	public List<ArticleLiteDto> getByCategory(int categoryId) throws Exception {
		con = getCon();
		List<ArticleLiteDto> articles = new ArrayList<ArticleLiteDto>();
		String sql = "SELECT articleId,title,pubDate FROM t_article WHERE categoryId=? ORDER BY pubDate desc";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, categoryId);
		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			ArticleLiteDto article = new ArticleLiteDto();
			article.setId(rs.getInt("articleId"));
			article.setTitle(rs.getString("title"));
			article.setPubDate(DateUtil.formatString(rs.getString("pubDate"),
					"yyyy-MM-dd HH:mm:ss"));
			articles.add(article);
		}
		close(con);
		return articles;
	}

	public List<ArticleLiteDto> archive() throws Exception {
		con = getCon();

		List<ArticleLiteDto> articles = new ArrayList<ArticleLiteDto>();
		String sql = "SELECT articleId,title,pubDate FROM t_article WHERE categoryId>0 ORDER BY pubDate desc";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		while (rs.next()) {
			ArticleLiteDto article = new ArticleLiteDto();
			article.setId(rs.getInt("articleId"));
			article.setTitle(rs.getString("title"));
			article.setPubDate(DateUtil.formatString(rs.getString("pubDate"),
					"yyyy-MM-dd HH:mm:ss"));
			articles.add(article);
		}
		close(con);
		return articles;
	}

	public AboutDto getAbout() throws Exception {
		con = getCon();

		String sql = "SELECT content FROM t_article WHERE categoryId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, 0);
		ResultSet rs = pstmt.executeQuery();
		AboutDto article = new AboutDto();

		if (rs.next()) {
			article.setContent(rs.getString("content"));
		}

		close(con);
		return article;

	}

	public void updateAbout(AboutDto about) throws Exception {
		con = getCon();

		String sql = "update t_article set  content=? where categoryId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, about.getContent());
		pstmt.setInt(2, 0);
		pstmt.executeUpdate();

		close(con);
	}

	public void saveAbout(AboutDto about) throws Exception {
		con = getCon();

		String sql = "insert into t_article values(null,0,1,'about',?,now(),0,'about','remark',0)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, about.getContent());
		pstmt.executeUpdate();

		close(con);
	}

	public void update(Article article) throws Exception {
		con = getCon();

		String sql = "update t_article set categoryId=?,title=?,content=?,remark=?,picture=? where articleId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, article.getCategoryId());
		pstmt.setString(2, article.getTitle());
		pstmt.setString(3, article.getContent());
		pstmt.setString(4, article.getRemark());
		pstmt.setString(5, article.getPicture());
		pstmt.setInt(6, article.getId());
		pstmt.executeUpdate();

		close(con);

	}

	public void save(Article article) throws Exception {
		con = getCon();

		String sql = "insert into t_article values(null,?,1,?,?,now(),0,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, article.getCategoryId());
		pstmt.setString(2, article.getTitle());
		pstmt.setString(3, article.getContent());
		pstmt.setString(4, article.getRemark());
		pstmt.setString(5, article.getPicture());
		pstmt.setInt(6, article.getIsDraft());
		pstmt.executeUpdate();

		close(con);
	}

	public void delete(Integer id) throws Exception {
		con = getCon();

		String sql = "delete from t_article where articleId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, id);
		pstmt.executeUpdate();

		close(con);
	}

	public int count() throws Exception {
		con = getCon();
		String sql = "SELECT COUNT(*) AS total FROM t_article WHERE categoryId > 0;";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		int count = 0;
		if (rs.next()) {
			count = rs.getInt("total");
		}

		close(con);
		return count;
	}
}
